CREATE SEQUENCE
CREATE SEQUENCE — Define a new sequence generator
Synopsis
CREATE [ TEMPORARY | TEMP ] SEQUENCE [ IF NOT EXISTS ] name [ INCREMENT
[ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
[ OWNED BY { table_name.column_name | NONE } ]
Description
CREATE SEQUENCE creates a new sequence number generator. This involves creating and initializing a new special single-row table with the name name. The generator will be owned by the user who issues the command.
If a schema name is given, the sequence will be created in the specified schema. Otherwise, it will be created in the current schema. Temporary sequences exist in a special schema, so a schema name cannot be given when creating a temporary sequence. The sequence name must be distinct from the name of any other sequence, table, index, view, or foreign table in the same schema.
After the sequence is created, you can use the functions nextval, currval, and setval to operate on the sequence.
Although you cannot directly update a sequence, you can use a query like:
SELECT * FROM name;
to examine the sequence's parameters and current state. In particular, the sequence's last_value field shows the most recent value obtained by any session (of course, this value may be stale by the time it is printed, as other sessions may be executing nextval calls).
Parameters
TEMPORARY or TEMP
If specified, the sequence object is created only for this session and is automatically dropped when the session exits. While a temporary sequence exists, an existing permanent sequence with the same name becomes invisible (in this session), though the permanent sequence can still be referenced using a schema-qualified name.
IF NOT EXISTS
Do not throw an error if a relation with the same name already exists. A notice will be issued in this case. Note that there is no guarantee that the existing relation is at all similar to the sequence to be created — it may not even be a sequence.
name
The name of the sequence to be created (can be schema-qualified).
data_type
The optional clause AS data_type specifies the data type of the sequence. Valid types are smallint, integer, and bigint. The default is bigint. The data type determines the default minimum and maximum values of the sequence.
increment
The optional clause INCREMENT BY increment specifies the value to be added to the current sequence value to produce a new value.
A positive value creates an ascending sequence; a negative value creates a descending sequence. The default value is 1.
minvalue
NO MINVALUE
The optional clause MINVALUE minvalue determines the minimum value the sequence can produce. If this clause is not provided or NO MINVALUE is specified, the default value is used. The default for an ascending sequence is 1. The default for a descending sequence is the minimum value of the data type.
maxvalue
NO MAXVALUE
The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If this clause is not provided or NO MAXVALUE is specified, the default value is used. The default for an ascending sequence is the maximum value of the data type. The default for a descending sequence is -1.
start
The optional clause START WITH start allows the sequence to start from any point. For ascending and descending sequences, the default start values are minvalue and maxvalue, respectively.
cache
The optional clause CACHE cache specifies how many sequence numbers to pre-allocate and keep in memory for fast access. The minimum value is 1 (generating only one value at a time, i.e., no caching), which is also the default.
CYCLE
NO CYCLE
For ascending and descending sequences, the CYCLE option allows the sequence to wrap around when maxvalue and minvalue are reached, respectively. If the limit is reached, the next generated number will be minvalue and maxvalue, respectively.
If NO CYCLE is specified, any nextval call after the sequence reaches its maximum value will return an error. If neither CYCLE nor NO CYCLE is specified, the default is NO CYCLE.
OWNED BY table_name.column_name
OWNED BY NONE
The OWNED BY option causes the sequence to be associated with a specific table column, so that if the column (or the entire table) is dropped, the sequence will also be automatically dropped. The specified table must have the same owner as the sequence and be in the same schema. The default option OWNED BY NONE specifies that the sequence is not associated with any column.
Notes
Use DROP SEQUENCE to remove a sequence.
Sequences are based on bigint arithmetic, so the range cannot exceed that of an eight-byte integer (-9223372036854775808 to 9223372036854775807).
Since nextval and setval calls are never rolled back, sequence objects cannot be used if "gapless" assignment of serial numbers is required. Gapless assignment can be built using exclusive locks on a table containing only a counter, but this scheme is much more expensive than sequence objects, especially when many transactions concurrently request serial numbers.
If a cache setting greater than 1 is used for a sequence object that will be used concurrently by multiple sessions, you may get unexpected results. Each session allocates and caches subsequent sequence values when accessing the sequence object, and correspondingly increments the sequence object's last_value. Then, the next nextval within that session does cache-1 and simply returns a pre-allocated value without modifying the sequence object. Therefore, any allocated but unused numbers in a session will be lost when the session ends, resulting in "holes" in the sequence.
Furthermore, although multiple sessions can allocate different sequence values, these values may not be generated in order when all sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before A generates nextval=2. Therefore, if the cache setting is 1, you can safely assume that nextval values are generated sequentially. If the cache setting is greater than 1, you can only assume that nextval values are distinct, but not that they are generated in strictly sequential order. Also, last_value reflects the last value served to any session, regardless of whether it has been returned by nextval. Another consideration is that a setval executed on such a sequence will not notify other sessions until they have exhausted any cached pre-allocated values.
Examples
Create an ascending sequence named serial, starting from 101:
CREATE SEQUENCE serial START 101;
Select the next number from this sequence:
SELECT nextval('serial');
nextval
---------
101
Select the next number from this sequence again:
SELECT nextval('serial');
nextval
---------
102
Use this sequence in an INSERT command:
INSERT INTO distributors VALUES (nextval('serial'), 'nothing');
Update the new column values after a COPY FROM:
BEGIN;
COPY distributors FROM 'input_file';
SELECT setval('serial', max(id)) FROM distributors;
END;